<?php
class Voucher_model extends MY_Model {
	
	public $table = 'VOUCHERS';
	public $join = ' left join USERS A on A.ID = VOUCHERS.USER_ID left join VOUCHER_TYPES C on C.ID = VOUCHERS.VOUCHER_TYPE_ID left join VOUCHER_MATCHS D on D.ID = VOUCHERS.VOUCHER_MATCH_ID';
	public $select = 'VOUCHERS.*,A.FIRSTNAME USER_FIRSTNAME,A.LASTNAME USER_LASTNAME,A.POSITION USER_POSITION,C.NAME VOUCHER_TYPE_NAME,D.NAME VOUCHER_MATCH_NAME';
	
    function __construct()
    {
        parent::__construct();
    }
	
	function get_balance($fund_id,$agency_id,$y,$m)
	{
		$balance = $this->where('YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.($m + 1).' and vouchers.is_balance = 1 and vouchers.agency_id = '.$agency_id.' and vouchers.fund_id = '.$fund_id)->get();
		$where = ' and vouchers.agency_id = '.$agency_id.' and vouchers.fund_id = '.$fund_id;
		if(!$balance)
		{
			$sql = 'select accounts.id,
			(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) < 0 then 0 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) end) dr_sum,
			(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) * -1 else 0 end) cr_sum 
			from accounts 
			left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and vouchers.mode is null) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) a on a.account_id = accounts.id 
			left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and vouchers.mode is null) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) b on b.account_id = accounts.id 
			left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and vouchers.mode is null) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) c on c.account_id = accounts.id 
			left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and vouchers.mode is null) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) d on d.account_id = accounts.id  
			where accounts.fund_id = '.$fund_id;
			//echo nl2br($sql);
			return $this->get($sql,FALSE);
		}
		else
		{
			return FALSE;
		}
		
	}
	
	function get_close_balance($fund_id,$agency_id,$y,$m)
	{
		$balance = $this->where('YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.($m + 1).' and vouchers.is_balance = 1 and vouchers.agency_id = '.$agency_id.' and vouchers.fund_id = '.$fund_id)->get();
		$where = ' and vouchers.agency_id = '.$agency_id.' and vouchers.fund_id = '.$fund_id;
		if(!$balance)
		{
			$sql = 'select accounts.id,
			(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) < 0 then 0 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) end) dr_sum,
			(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) * -1 else 0 end) cr_sum 
			from accounts 
			left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode in (\'edit\',\'closed\',\'depreciation\')) ) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) a on a.account_id = accounts.id 
			left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode in (\'edit\',\'closed\',\'depreciation\')) ) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) b on b.account_id = accounts.id 
			left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and vouchers.mode is null) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) c on c.account_id = accounts.id 
			left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and vouchers.mode is null) and YEAR(vouchers.voucher_date) = '.$y.' and MONTH(vouchers.voucher_date) = '.$m.' '.$where.' group by account_id) d on d.account_id = accounts.id  
			';
			return $this->get($sql,FALSE);
		}
		else
		{
			return FALSE;
		}
		
	}
	
}
?>